﻿using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.Logging;
using StackExchange.Profiling;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Fast.Extensions.DatabaseAccessor.EntityFrameworkCore
{
    /// <summary>
    /// DatabaseFacade 扩展类
    /// </summary>
    [SkipScan]
    public static class DatabaseFacadeExtensions
    {
        #region PrivateMethods
        /// <summary>
        /// 创建数据库命令对象
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>命令对象</returns>
        /// <exception cref="InvalidOperationException"></exception>
        /// <exception cref="ArgumentNullException"></exception>
        private static DbCommand CreateDbCommand(this DatabaseFacade databaseFacade, string sql, CommandType commandType = CommandType.Text)
        {
            // 判断是否是关系型数据库
            if (!databaseFacade.IsRelational()) throw new InvalidOperationException("ADO.NET操作只支持关系型数据库");

            if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentNullException(nameof(sql));

            // 检查是否支持存储过程
            DatabaseHelper.CheckStoredProcedureSupported(databaseFacade.ProviderName, commandType);

            // 获取数据库连接对象
            var dbConnection = databaseFacade.GetDbConnection();

            // 创建数据库命令对象
            var dbCommand = dbConnection.CreateCommand();

            // 设置基本参数
            dbCommand.Transaction = databaseFacade.CurrentTransaction?.GetDbTransaction();
            dbCommand.CommandType = commandType;
            dbCommand.CommandText = sql;

            // 设置超时
            var commandTimeout = databaseFacade.GetCommandTimeout();
            if (commandTimeout != null) dbCommand.CommandTimeout = commandTimeout.Value;

            // 返回
            return dbCommand;
        }

        /// <summary>
        /// 设置数据库命令对象的参数
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="dbCommand">数据库命令对象</param>
        /// <param name="parameters">命令参数</param>
        private static void SetDbParameters(string providerName, ref DbCommand dbCommand, DbParameter[] parameters)
        {
            if (parameters == null || parameters.Length == 0) return;

            // 添加命令参数前缀
            foreach (var parameter in parameters)
            {
                parameter.ParameterName = FixSqlParameterPlaceholder(providerName, parameter.ParameterName);
                dbCommand.Parameters.Add(parameter);
            }
        }
        /// <summary>
        /// 设置数据库命令对象的参数
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="dbCommand">数据库命令对象</param>
        /// <param name="parameters">命令参数</param>
        private static void SetDbParameters(string providerName, ref DbCommand dbCommand, object model)
        {
            var dbParameters = ConvertToDbParameters(model, dbCommand);
            SetDbParameters(providerName, ref dbCommand, dbParameters);
        }
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="dbCommand"></param>
        private static void OpenConnection(DatabaseFacade databaseFacade, DbCommand dbCommand)
        {
            // 判断连接字符串是否关闭，如果是，则开启
            if (dbCommand.Connection?.State == ConnectionState.Closed)
            {
                dbCommand.Connection.Open();

                // 打印数据库连接信息到 MiniProfiler
                PrintConnectionToMiniProfiler(databaseFacade, dbCommand.Connection, false);
            }
        }
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="dbCommand"></param>
        /// <param name="cancellationToken">异步取消令牌</param>
        /// <returns></returns>
        private static async Task OpenConnectionAsync(DatabaseFacade databaseFacade, DbCommand dbCommand, CancellationToken cancellationToken = default)
        {
            // 判断连接字符串是否关闭，如果是，则开启
            if (dbCommand.Connection?.State == ConnectionState.Closed)
            {
                await dbCommand.Connection.OpenAsync();

                // 打印数据库连接信息到 MiniProfiler
                PrintConnectionToMiniProfiler(databaseFacade, dbCommand.Connection, true);
            }
        }
        /// <summary>
        /// 修复不同数据库命令参数前缀不一致问题
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="parameterName"></param>
        /// <param name="isFixed"></param>
        /// <returns></returns>
        private static string FixSqlParameterPlaceholder(string providerName, string parameterName, bool isFixed = true)
        {
            var placeholder = !DatabaseHelper.IsTargetDatabase(providerName, DatabaseHelper.Oracle) ? "@" : ":";
            if (parameterName.StartsWith("@") || parameterName.StartsWith(":"))
            {
                parameterName = parameterName[1..];
            }

            return isFixed ? placeholder + parameterName : parameterName;
        }
        /// <summary>
        /// 将模型转为 DbParameter 集合
        /// </summary>
        /// <param name="model">参数模型</param>
        /// <param name="dbCommand">数据库命令对象</param>
        /// <returns></returns>
        private static DbParameter[] ConvertToDbParameters(object model, DbCommand dbCommand)
        {
            var modelType = model?.GetType();

            // 处理字典类型参数
            if (modelType == typeof(Dictionary<string, object>)) return ConvertToDbParameters((Dictionary<string, object>)model, dbCommand);

            var dbParameters = new List<DbParameter>();
            if (model == null || !modelType.IsClass) return dbParameters.ToArray();

            // 获取所有公开实例属性
            var properties = modelType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
            if (properties.Length == 0) return dbParameters.ToArray();

            // 遍历所有属性
            foreach (var property in properties)
            {
                var propertyValue = property.GetValue(model) ?? DBNull.Value;

                // 创建命令参数
                var dbParameter = dbCommand.CreateParameter();

                //// 判断属性是否贴有 [DbParameter] 特性
                //if (property.IsDefined(typeof(DbParameterAttribute), true))
                //{
                //    var dbParameterAttribute = property.GetCustomAttribute<DbParameterAttribute>(true);
                //    dbParameters.Add(ConfigureDbParameter(property.Name, propertyValue, dbParameterAttribute, dbParameter));
                //    continue;
                //}

                dbParameter.ParameterName = property.Name;
                dbParameter.Value = propertyValue;

                dbParameters.Add(dbParameter);
            }

            return dbParameters.ToArray();
        }
        /// <summary>
        /// 将字典转换成命令参数
        /// </summary>
        /// <param name="keyValues">字典</param>
        /// <param name="dbCommand">数据库命令对象</param>
        /// <returns></returns>
        private static DbParameter[] ConvertToDbParameters(Dictionary<string, object> keyValues, DbCommand dbCommand)
        {
            var dbParameters = new List<DbParameter>();
            if (keyValues == null || keyValues.Count == 0) return dbParameters.ToArray();

            foreach (var key in keyValues.Keys)
            {
                var value = keyValues[key] ?? DBNull.Value;

                // 创建命令参数
                var dbParameter = dbCommand.CreateParameter();
                dbParameter.ParameterName = key;
                dbParameter.Value = value;
                dbParameters.Add(dbParameter);
            }

            return dbParameters.ToArray();
        }
        /// <summary>
        /// 打印数据库连接信息到 MiniProfiler
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="dbConnection">数据库连接对象</param>
        /// <param name="isAsync"></param>
        private static void PrintConnectionToMiniProfiler(DatabaseFacade databaseFacade, DbConnection dbConnection, bool isAsync)
        {
            // 打印数据库连接信息
            App.PrintToMiniProfiler(MiniProfilerCategory.Sql.GetDescription(), $"Open{(isAsync ? "Async" : string.Empty)}", $"Connection Open{(isAsync ? "Async" : string.Empty)}()");

            ////if (IsDevelopment && IsPrintDbConnectionInfo)
            ////{
            //var connectionId = databaseFacade.GetService<IRelationalConnection>()?.ConnectionId;
            //// 打印连接信息消息
            //App.PrintToMiniProfiler(MiniProfilerCategory.Connection.GetDescription(), "Information", $"[Connection Id: {connectionId}] / [Database: {dbConnection.Database}] / [Connection String: {dbConnection.ConnectionString}]");
            ////}
        }
        #endregion

        #region PublicMethods
        /// <summary>
        /// 构建DbCommand对象
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="parameters">命令参数</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>命令对象</returns>
        public static DbCommand BuildDbCommand(this DatabaseFacade databaseFacade, string sql, DbParameter[] parameters, CommandType commandType = CommandType.Text)
        {
            // 创建数据库命令对象
            var dbCommand = databaseFacade.CreateDbCommand(sql, commandType);
            //设置数据库命令对象的参数
            SetDbParameters(databaseFacade.ProviderName, ref dbCommand, parameters);
            //打开数据库连接
            OpenConnection(databaseFacade, dbCommand);

            return dbCommand;
        }
        /// <summary>
        /// 构建DbCommand对象
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="model">命令参数</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>命令对象</returns>
        public static DbCommand BuildDbCommand(this DatabaseFacade databaseFacade, string sql, object model, CommandType commandType = CommandType.Text)
        {
            // 创建数据库命令对象
            var dbCommand = databaseFacade.CreateDbCommand(sql, commandType);
            //设置数据库命令对象的参数
            SetDbParameters(databaseFacade.ProviderName, ref dbCommand, model);
            //打开数据库连接
            OpenConnection(databaseFacade, dbCommand);

            return dbCommand;
        }
        /// <summary>
        /// 构建DbCommand对象
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="parameters">命令参数</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="cancellationToken">异步取消令牌</param>
        /// <returns>命令对象</returns>
        public static async Task<DbCommand> BuildDbCommandAsync(this DatabaseFacade databaseFacade, string sql, DbParameter[] parameters, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)
        {
            // 创建数据库命令对象
            var dbCommand = databaseFacade.CreateDbCommand(sql, commandType);
            //设置数据库命令对象的参数
            SetDbParameters(databaseFacade.ProviderName, ref dbCommand, parameters);
            //打开数据库连接
            await OpenConnectionAsync(databaseFacade, dbCommand, cancellationToken);

            return dbCommand;
        }
        /// <summary>
        /// 构建DbCommand对象
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="model">命令参数</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>命令对象</returns>
        public static async Task<DbCommand> BuildDbCommandAsync(this DatabaseFacade databaseFacade, string sql, object model, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)
        {
            // 创建数据库命令对象
            var dbCommand = databaseFacade.CreateDbCommand(sql, commandType);
            //设置数据库命令对象的参数
            SetDbParameters(databaseFacade.ProviderName, ref dbCommand, model);
            //打开数据库连接
            await OpenConnectionAsync(databaseFacade, dbCommand, cancellationToken);

            return dbCommand;
        }
        #endregion
    }
}
